package database;

import com.mysql.jdbc.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Martin Mohr Pedersen
 */
public class DBConnection {

    private static DBConnection instance;
    private Connection connection;
    private boolean debug;
    
    private String hostname;
    private String schema;
    private String username;
    private String password;

    private DBConnection() {
        debug = false;
        
        hostname = "127.0.0.1";
        schema = "svsinfo";
        username = "root";
        password = "";
    }
    
    /**
     * Fetches the singleton for the database
     */
    public static DBConnection getInstance() {

        if (instance == null) {
            instance = new DBConnection();
        }

        return instance;
    }
    
    public void setDebug(boolean bool) {
        this.debug = bool;
    }
    
    public void setHostname(String hostname) {
         this.hostname = hostname;
    }
    
    public void setSchema(String schema) {
        this.schema = schema;
    }
    
    /**
     * Opens the connection to the database based on current configuration
     */
    public void open() throws SQLException {
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (Exception ex) {
            System.out.println(ex.getCause());
        }
        
        String connectionFormat = "jdbc:mysql://%s/%s?user=%s&password=%s";
        
        String connectionUrl = String.format(connectionFormat, hostname, schema, username, password);
        
        connection = DriverManager.getConnection(connectionUrl);
        
    }

    /**
     * Closes the database connection
     */
    public void close() {
        try {
            connection.close();
        } catch (SQLException ex) {
            Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    /**
     * Checks if the connection to the database is open
     * @return true if the connection is open, false otherwise
     */
    public boolean isOpen() {
        
        if(connection != null) {
            try {
               return !connection.isClosed();
            } catch (SQLException ex) {
                return false;
            }
        } else
            return false;
    }
    
    /**
     * Executes a SQL insert command
     * @param sql The sql to use
     * @param args The arguments to use in the query
     * @return The auto-generated ID, null if no ID was generated
     */
    public Integer executeInsert(String sql, Object[] args) {    
        
        Integer generatedId = null;
        
        try {
            open();
            PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            bindArgs(pstmt, args);
            logSql(sql, args);
            pstmt.execute();
            ResultSet keys = pstmt.getGeneratedKeys();
            if(keys.next()) {
                generatedId = keys.getInt(1);
            }
            
            pstmt.close();
            close();
        } catch (SQLException ex) {
            Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return generatedId;
         
    }
    
    /**
     * Binds all given arguments to the prepared statement
     * @param pstmt
     * @param params
     * @throws SQLException 
     */
    protected void bindArgs(PreparedStatement pstmt, Object[] params) throws SQLException {
        int index = 1;
        for (Object param : params) {
            pstmt.setObject(index, param);
            index++;
        }
        
    }
    
    /**
     * Executes a SQL select query and returns the results as a list with maps
     * @param sql SQL string to use
     * @param args Arguments to bind with the SQL string
     * @return 
     */
    public ArrayList<HashMap<String, Object>> executeSelect(String sql, Object[] args) {
        
        try {
            open();
            PreparedStatement pstmt = connection.prepareStatement(sql);
            bindArgs(pstmt, args);
            ResultSet rs = pstmt.executeQuery();
            logSql(sql, args);
            
            ArrayList<HashMap<String, Object>> rows = new ArrayList<HashMap<String, Object>>();
            while(rs.next()) {
                rows.add(unpackResultRow(rs));
            }
            
            rs.close();
            close();
            
            return rows;
            
        } catch (SQLException ex) {
            Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return null;
    }
    
    /**
     * Same as executeSelect(sql, args), but without arguments
     * @param sql
     * @return 
     */
    public ArrayList<HashMap<String, Object>> executeSelect(String sql) {
        return executeSelect(sql, new Object[] {});
    }
    
    /**
     * Unpacks the current row in the resultset and returns it as a hashmap, 
     * where the column names (from the row) are used as index keys in the hashmap.
     * @param rs
     * @return
     * @throws SQLException 
     */
    private HashMap<String, Object> unpackResultRow(ResultSet rs) throws SQLException {
        ResultSetMetaData meta = rs.getMetaData();
        
        int columns = meta.getColumnCount();
        
        HashMap<String, Object> row = new HashMap<String, Object>();
        String label;
        int type;
        Object value;
        for(int i = 1; i <= columns; i++) {
            label = meta.getColumnLabel(i);
            type = meta.getColumnType(i);
            
            switch(type){                
                default:
                    value = rs.getObject(i);
                    break;
            }
            
            row.put(label, value);
        }
        
        return row;
    }
    
    /**
     * Logs sql queries
     * @param sql
     * @param args 
     */
    private void logSql(String sql, Object[] args) {
        
        if(!debug) {
            return;
        }
        
        String argString = "";
        
        if(args.length > 0) {
            
            argString += " (";
            
            for(Object arg : args) {
                argString += arg + ",";
            }
            
            argString += ")";
            
        }
        
        System.out.println(sql +  argString);
    }
}
